package com.marchsoft.musicalstore.repository;

import com.marchsoft.musicalstore.entity.OrderMy;
import com.marchsoft.musicalstore.entity.Orders;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: 源
 * Date: 2019-11-17
 * Time: 11:20
 */
public interface OrderRepository extends JpaRepository<OrderMy,Integer> {
    /**
     * 根据用户id和商品状态查询订单
     *
     * @param id    用户id
     * @param isout 订单状态
     * @return
     */

    @Query(value = "(select ord.id,ord.buy_type,sel.name,ord.user_id,ord.product_id,ord.is_out,ord.price_count,ord.order_pic,ord.product_num from" +
            " ordermy ord left join selclassable sel on ord.product_id=sel.id where ord.user_id=:id and ord.is_out=:isout and ord.buy_type=0 ) union" +
            " (select ord.id AS id,ord.buy_type AS buyType,cor.name AS name,ord.user_id AS userId,ord.product_id AS productId,ord.is_out AS isOut,ord.price_count AS priceCount,ord.order_pic AS orderPic,ord.product_num AS productNum from" +
            " ordermy ord left join instrument ins on ord.product_id=ins.id left join coursetype cor on ins.type=cor.id where ord.user_id=1 " +
            " and ord.is_out=0 and ord.buy_type=1)", nativeQuery = true)
    List<Map<String, Object>> getAllByUserIdAndIsOut(@Param("id") Integer id, @Param("isout") Integer isout);

    @Query(value = "(select ord.id,ord.buy_type,sel.name,ord.user_id,ord.product_id,ord.is_out,ord.price_count,ord.order_pic,ord.product_num from" +
            " ordermy ord left join selclassable sel on ord.product_id=sel.id where ord.user_id=:id and ord.buy_type=0 ) union" +
            " (select ord.id,ord.buy_type,cor.name,ord.user_id,ord.product_id,ord.is_out,ord.price_count,ord.order_pic,ord.product_num from" +
            " ordermy ord left join instrument ins on ord.product_id=ins.id left join coursetype cor on ins.type=cor.id where ord.user_id=:id " +
            " and ord.buy_type=1)",nativeQuery = true)
    List<Map<String, Object>> getByUserId(@Param("id") Integer id);

    Optional<OrderMy> findById(Integer id);
}
